package com.whnk.attendance.service.impl;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.whnk.attendance.vo.*;
import com.whnk.common.core.utils.DateUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.whnk.attendance.mapper.MonthIncomeStatisMapper;
import com.whnk.attendance.domain.MonthIncomeStatis;
import com.whnk.attendance.service.IMonthIncomeStatisService;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;

/**
 * 每月统计收入Service业务层处理
 * 
 * @author whnk
 * @date 2021-11-17
 */
@Service
public class MonthIncomeStatisServiceImpl implements IMonthIncomeStatisService 
{
    private static final Logger logger = LoggerFactory.getLogger(MonthIncomeStatisServiceImpl.class);

    @Autowired
    private MonthIncomeStatisMapper monthIncomeStatisMapper;

    /**
     * 查询每月统计收入
     * 
     * @param incomeId 每月统计收入主键
     * @return 每月统计收入
     */
    @Override
    public MonthIncomeStatis selectMonthIncomeStatisByIncomeId(Integer incomeId)
    {
        return monthIncomeStatisMapper.selectMonthIncomeStatisByIncomeId(incomeId);
    }

    /**
     * 查询每月统计收入列表
     * 
     * @param monthIncomeStatis 每月统计收入
     * @return 每月统计收入
     */
    @Override
    public List<MonthIncomeStatis> selectMonthIncomeStatisList(MonthIncomeStatis monthIncomeStatis)
    {
        return monthIncomeStatisMapper.selectMonthIncomeStatisList(monthIncomeStatis);
    }

    /**
     * 新增每月统计收入
     * 
     * @param monthIncomeStatis 每月统计收入
     * @return 结果
     */
    @Override
    public int insertMonthIncomeStatis(MonthIncomeStatis monthIncomeStatis)
    {
        monthIncomeStatis.setCreateTime(DateUtils.getNowDate());
        return monthIncomeStatisMapper.insertMonthIncomeStatis(monthIncomeStatis);
    }

    /**
     * 修改每月统计收入
     * 
     * @param monthIncomeStatis 每月统计收入
     * @return 结果
     */
    @Override
    public int updateMonthIncomeStatis(MonthIncomeStatis monthIncomeStatis)
    {
        monthIncomeStatis.setUpdateTime(DateUtils.getNowDate());
        return monthIncomeStatisMapper.updateMonthIncomeStatis(monthIncomeStatis);
    }

    /**
     * 批量删除每月统计收入
     * 
     * @param incomeIds 需要删除的每月统计收入主键
     * @return 结果
     */
    @Override
    public int deleteMonthIncomeStatisByIncomeIds(Integer[] incomeIds)
    {
        return monthIncomeStatisMapper.deleteMonthIncomeStatisByIncomeIds(incomeIds);
    }

    /**
     * 删除每月统计收入信息
     * 
     * @param incomeId 每月统计收入主键
     * @return 结果
     */
    @Override
    public int deleteMonthIncomeStatisByIncomeId(Integer incomeId)
    {
        return monthIncomeStatisMapper.deleteMonthIncomeStatisByIncomeId(incomeId);
    }

    //批处理每月收入统计
    public void batchComputer(String month){

        //删除该月份的原始数据
        monthIncomeStatisMapper.deleteMonthIncomeStatisVo(month);

        /**
         * 1.1.创建一个数据源list，包含区域名称t4.area_name,项目分类名称t3.pro_cla_name，人数countNum
         * 查询考勤表pm_sign_info，项目列表pm_pro_info，项目分类列表project_classify_info，区域列表area_info
         */
        List<MonthIncomeStatisEmpVo> monthIncomeStatisEmpVos = monthIncomeStatisMapper.queryMonthIncomeStatisEmp(month);

        /**
         * 1.2.创建一个数据源list，包含区域名称t4.area_name,项目分类名称t3.pro_cla_name，离职人数countNum
         * 查询考勤表pm_sign_info，项目列表pm_pro_info，项目分类列表project_classify_info，区域列表area_info，人员基本信息表emp_base_info
         */
        List<MonthIncomeStatisEmpVo> monthIncomeStatisDepartVos = monthIncomeStatisMapper.queryMonthIncomeStatisDepart(month);

        /**
         * 1.3.创建一个数据源list，包含区域名称t4.area_name,项目分类名称t3.pro_cla_name， 参考人员reference_month
         */
        List<MonthIncomeStatisMDVo> monthIncomeStatisMDVos = monthIncomeStatisMapper.queryMonthIncomeStatisMD(month);

        /**
         * 1.4.创建一个数据源list，包含区域名称t4.area_name,项目分类名称t3.pro_cla_name， 工作量收入workload_income， 人力成本预估humen_cost_forecast， 毛利gross_profit
         */
        List<MonthIncomeStatisWHGVo> monthIncomeStatisWHGVos = monthIncomeStatisMapper.queryMonthIncomeStatisWHG(month);


        /**
         * 2.遍历上面的list，创建第一个map<area_name:(pro_cla_name，value)>，其他map(area_name+pro_cla_name:value)
         */
        //平均人数
        Map<String,Map<String,Integer>> empMap = new LinkedHashMap<>();

        for(MonthIncomeStatisEmpVo monthIncomeStatisEmpVo : monthIncomeStatisEmpVos){
            String areaName = monthIncomeStatisEmpVo.getAreaName();
            String proName = monthIncomeStatisEmpVo.getProClaName();
            int averageHumen = monthIncomeStatisEmpVo.getAverageHumen();

            Map<String,Integer> proClaMap = empMap.get(areaName);
            if(CollectionUtils.isEmpty(proClaMap)){
                proClaMap = new LinkedHashMap<>();
                proClaMap.put(proName, averageHumen);
            }else{
                proClaMap.put(proName, averageHumen);
            }

            empMap.put(monthIncomeStatisEmpVo.getAreaName(),proClaMap);
        }

        //离职人数
        Map<String,Integer> departMap = new LinkedHashMap<>();

        for(MonthIncomeStatisEmpVo monthIncomeStatisEmpVo : monthIncomeStatisDepartVos){
            String key = monthIncomeStatisEmpVo.getAreaName()+monthIncomeStatisEmpVo.getProClaName();
            departMap.put(key,monthIncomeStatisEmpVo.getAverageHumen());
        }

        //参考人月
        Map<String,BigDecimal> mdMap = new LinkedHashMap<>();

        for(MonthIncomeStatisMDVo monthIncomeStatisMDVo : monthIncomeStatisMDVos){
            String key = monthIncomeStatisMDVo.getAreaName()+monthIncomeStatisMDVo.getProClaName();
            mdMap.put(key,monthIncomeStatisMDVo.getReferenceMonth());
        }

        //工作量收入
        Map<String,BigDecimal> wiMap = new LinkedHashMap<>();

        for(MonthIncomeStatisWHGVo monthIncomeStatisWHGVo : monthIncomeStatisWHGVos){
            String key = monthIncomeStatisWHGVo.getAreaName()+monthIncomeStatisWHGVo.getProClaName();
            wiMap.put(key,monthIncomeStatisWHGVo.getWorkloadIncome());
        }

        //人力成本预估
        Map<String,BigDecimal> hcfMap = new LinkedHashMap<>();

        for(MonthIncomeStatisWHGVo monthIncomeStatisWHGVo : monthIncomeStatisWHGVos){
            String key = monthIncomeStatisWHGVo.getAreaName()+monthIncomeStatisWHGVo.getProClaName();
            hcfMap.put(key,monthIncomeStatisWHGVo.getHumenCostForecast());
        }

        //毛利
        Map<String,BigDecimal> gpMap = new LinkedHashMap<>();

        for(MonthIncomeStatisWHGVo monthIncomeStatisWHGVo : monthIncomeStatisWHGVos){
            String key = monthIncomeStatisWHGVo.getAreaName()+monthIncomeStatisWHGVo.getProClaName();
            gpMap.put(key,monthIncomeStatisWHGVo.getGrossProfit());
        }


        /**
         * 3.创建明细的list、小计,总计的Vo
         */
        List<MonthIncomeStatisVo> mingxiList = new ArrayList<>();

        MonthIncomeStatisVo xiaoji = new MonthIncomeStatisVo();

        MonthIncomeStatisVo zongji = new MonthIncomeStatisVo();


        /**
         * 4.build明细的list，小计、总计的Vo
         * 遍历的一个map，在里层遍历中构建明细Vo，累加小计Vo，在外层遍历构建中构建小计Vo累加总计Vo，遍历外层构建总计Vo
         * 明细Vo：设置key:区域名称+项目分类名称
         *
         *    加入区域名称
         *    加入项目分类名称
         *   平均人数：当前值的人数 - 第二个map的key相同离职人数
         *   参考人月：第三个map的key相同的参考人月
         *   工作量收入：第四个map的key相同的工作量收入
         *   平均单价：Vo的工作量收入/参考人月
         *   人力成本预估: 第四个map的key相同的人力成本预估
         *   毛利：第四个map的key相同的毛利
         *   毛利率：毛 利/工作量收入
         *
         *   将明细Vo加入明细list
         *
         *   外层遍历中构建小计Vo，加入明细的list
         *
         *   构建总计Vo，在外层遍历中累加小计Vo
         */
        zongji = new MonthIncomeStatisVo();
        zongji.setAreaName("总计");
        zongji.setMonth(month);

        for(String areaName : empMap.keySet()){
            Map<String, Integer> proMap = empMap.get(areaName);
            xiaoji = new MonthIncomeStatisVo();

            xiaoji.setAreaName(areaName);
            xiaoji.setProClaName("小计");
            xiaoji.setMonth(month);

            for(String proClaNum : proMap.keySet()){
                String proClaKey = areaName + proClaNum;
                MonthIncomeStatisVo monthIncomeStatisVo = new MonthIncomeStatisVo();

                monthIncomeStatisVo.setAreaName(areaName);
                monthIncomeStatisVo.setProClaName(proClaNum);
                monthIncomeStatisVo.setMonth(month);

                //平均人数 = 当前月人数 - 离职人数
                for(String departKey : departMap.keySet()){
                    if(proClaKey.equals(departKey)){
                        monthIncomeStatisVo.setAverageHumen(proMap.get(proClaNum) - departMap.get(departKey));
                        break;
                    }else
                        monthIncomeStatisVo.setAverageHumen(proMap.get(proClaNum));
                }

                //小计平均人数累加
                if(xiaoji.getAverageHumen() == null){
                    xiaoji.setAverageHumen(monthIncomeStatisVo.getAverageHumen());
                }else if(monthIncomeStatisVo.getAverageHumen() == null) {
                    xiaoji.setAverageHumen(xiaoji.getAverageHumen());
                }else{
                    xiaoji.setAverageHumen(xiaoji.getAverageHumen() + monthIncomeStatisVo.getAverageHumen());
                }

                //参考人月
                for(String mdKey : mdMap.keySet()){
                    if(proClaKey.equals(mdKey)){
                        monthIncomeStatisVo.setReferenceMonth(mdMap.get(mdKey));
                        break;
                    }
                }

                //小计参考人月累加
                if(xiaoji.getReferenceMonth() == null){
                    xiaoji.setReferenceMonth(monthIncomeStatisVo.getReferenceMonth());
                }else if(monthIncomeStatisVo.getReferenceMonth() == null) {
                    xiaoji.setReferenceMonth(xiaoji.getReferenceMonth());
                }else{
                    xiaoji.setReferenceMonth(xiaoji.getReferenceMonth().add(monthIncomeStatisVo.getReferenceMonth()));
                }

                //工作量收入
                for(String wiKey : wiMap.keySet()){
                    if(proClaKey.equals(wiKey)){
                        monthIncomeStatisVo.setWorkloadIncome(wiMap.get(wiKey));
                        break;
                    }
                }

                //小计工作量收入累加
                if(xiaoji.getWorkloadIncome() == null){
                    xiaoji.setWorkloadIncome(monthIncomeStatisVo.getWorkloadIncome());
                }else if(monthIncomeStatisVo.getWorkloadIncome() == null) {
                    xiaoji.setWorkloadIncome(xiaoji.getWorkloadIncome());
                }else{
                    xiaoji.setWorkloadIncome(xiaoji.getWorkloadIncome().add(monthIncomeStatisVo.getWorkloadIncome()));
                }

                //平均单价 = 工作量收入 / 参考人月
                if(monthIncomeStatisVo.getReferenceMonth() == null || monthIncomeStatisVo.getReferenceMonth().compareTo(BigDecimal.ZERO) == 0) {
                    monthIncomeStatisVo.setAveragePrice(null);
                }else{
                    monthIncomeStatisVo.setAveragePrice(monthIncomeStatisVo.getWorkloadIncome().divide(monthIncomeStatisVo.getReferenceMonth(), 2));
                }

                //人力成本预估
                for(String hcfKey : hcfMap.keySet()){
                    if(proClaKey.equals(hcfKey)){
                        monthIncomeStatisVo.setHumenCostForecast(hcfMap.get(hcfKey));
                        break;
                    }
                }

                //小计人力成本预估累加
                if(xiaoji.getHumenCostForecast() == null){
                    xiaoji.setHumenCostForecast(monthIncomeStatisVo.getHumenCostForecast());
                }else if(monthIncomeStatisVo.getHumenCostForecast() == null) {
                    xiaoji.setHumenCostForecast(xiaoji.getHumenCostForecast());
                }else{
                    xiaoji.setHumenCostForecast(xiaoji.getHumenCostForecast().add(monthIncomeStatisVo.getHumenCostForecast()));
                }

                //毛利
                for(String gpKey : gpMap.keySet()){
                    if(proClaKey.equals(gpKey)){
                        monthIncomeStatisVo.setGrossProfit(gpMap.get(gpKey));
                        break;
                    }
                }

                //小计毛利累加
                if(xiaoji.getGrossProfit() == null){
                    xiaoji.setGrossProfit(monthIncomeStatisVo.getGrossProfit());
                }else if(monthIncomeStatisVo.getGrossProfit() == null) {
                    xiaoji.setGrossProfit(xiaoji.getGrossProfit());
                }else{
                    xiaoji.setGrossProfit(xiaoji.getGrossProfit().add(monthIncomeStatisVo.getGrossProfit()));
                }

                //毛利率 = 毛利 / 工作量收入
                if(monthIncomeStatisVo.getWorkloadIncome() == null || monthIncomeStatisVo.getWorkloadIncome().compareTo(BigDecimal.ZERO) == 0) {
                    monthIncomeStatisVo.setGrossProfitMargin(null);
                }else{
                    monthIncomeStatisVo.setGrossProfitMargin(monthIncomeStatisVo.getGrossProfit().divide(monthIncomeStatisVo.getWorkloadIncome(), 2));
                }

                monthIncomeStatisVo.setType(0);

                mingxiList.add(monthIncomeStatisVo);
            }

            //小计平均单价
            if(xiaoji.getReferenceMonth() == null || xiaoji.getReferenceMonth().compareTo(BigDecimal.ZERO) == 0){
                xiaoji.setAveragePrice(null);
            }else {
                xiaoji.setAveragePrice(xiaoji.getWorkloadIncome().divide(xiaoji.getReferenceMonth(),2));
            }

            //小计毛利率
            if(xiaoji.getWorkloadIncome() == null || xiaoji.getWorkloadIncome().compareTo(BigDecimal.ZERO) == 0){
                xiaoji.setGrossProfitMargin(null);
            }else{
                xiaoji.setGrossProfitMargin(xiaoji.getGrossProfit().divide(xiaoji.getWorkloadIncome(),2));
            }

            xiaoji.setType(1);

            //总计平均人数累加
            if(zongji.getAverageHumen() == null){
                zongji.setAverageHumen(xiaoji.getAverageHumen());
            }else if(xiaoji.getAverageHumen() == null){
                zongji.setAverageHumen(zongji.getAverageHumen());
            }else {
                zongji.setAverageHumen(zongji.getAverageHumen() + xiaoji.getAverageHumen());
            }

            //总计参考人月累加
            if(zongji.getReferenceMonth() == null){
                zongji.setReferenceMonth(xiaoji.getReferenceMonth());
            }else if(xiaoji.getReferenceMonth() == null){
                zongji.setReferenceMonth(zongji.getReferenceMonth());
            }else{
                zongji.setReferenceMonth(zongji.getReferenceMonth().add(xiaoji.getReferenceMonth()));
            }

            //总计工作量收入累加
            if(zongji.getWorkloadIncome() == null){
                zongji.setWorkloadIncome(xiaoji.getWorkloadIncome());
            }else if(xiaoji.getWorkloadIncome() == null){
                zongji.setWorkloadIncome(zongji.getWorkloadIncome());
            }else {
                zongji.setWorkloadIncome(zongji.getWorkloadIncome().add(xiaoji.getWorkloadIncome()));
            }

            //总计人力成本预估累加
            if(zongji.getHumenCostForecast() == null){
                zongji.setHumenCostForecast(xiaoji.getHumenCostForecast());
            }else if(xiaoji.getHumenCostForecast() == null){
                zongji.setHumenCostForecast(zongji.getHumenCostForecast());
            }else {
                zongji.setHumenCostForecast(zongji.getHumenCostForecast().add(xiaoji.getHumenCostForecast()));
            }

            //总计毛利累加
            if(zongji.getGrossProfit() == null){
                zongji.setGrossProfit(xiaoji.getGrossProfit());
            }else if(xiaoji.getGrossProfit() == null){
                zongji.setGrossProfit(zongji.getGrossProfit());
            }else {
                zongji.setGrossProfit(zongji.getGrossProfit().add(xiaoji.getGrossProfit()));
            }

            mingxiList.add(xiaoji);
        }

        //总计平均单价
        if(zongji.getReferenceMonth() == null || zongji.getReferenceMonth().compareTo(BigDecimal.ZERO) == 0){
            zongji.setAveragePrice(null);
        }else {
            zongji.setAveragePrice(zongji.getWorkloadIncome().divide(zongji.getReferenceMonth(),2));
        }

        //总计毛利率
        if(zongji.getWorkloadIncome() == null || zongji.getWorkloadIncome().compareTo(BigDecimal.ZERO) == 0){
            zongji.setGrossProfitMargin(null);
        }else{
            zongji.setGrossProfitMargin(zongji.getGrossProfit().divide(zongji.getWorkloadIncome(),2));
        }

        zongji.setType(2);

        mingxiList.add(zongji);


        /**
         * 5. 将明细的list和总记的Vo合成一个list，调用insert批量导入数据库
         */
        for(MonthIncomeStatisVo monthIncomeStatisVo : mingxiList){
            monthIncomeStatisMapper.insertMonthIncomeStatisVo(monthIncomeStatisVo);
        }

    }

    //查询每月收入统计
    public MonthIncomeStatisJsonVo selectMonthIncomeStatisVoList(String month){

        MonthIncomeStatisJsonVo monthIncomeStatisJsonVo = new MonthIncomeStatisJsonVo();

        JSONArray tableData = new JSONArray();
        JSONArray tableMerge = new JSONArray();

        monthIncomeStatisJsonVo.setTableData(tableData);
        monthIncomeStatisJsonVo.setTableMerge(tableMerge);

        List<MonthIncomeStatisVo> monthIncomeStatisVos = monthIncomeStatisMapper.selectMonthIncomeStatisVoList(month);

        for(MonthIncomeStatisVo monthIncomeStatisVo : monthIncomeStatisVos){
            JSONObject dataRow = new JSONObject(true);
            dataRow.put("areaName",monthIncomeStatisVo.getAreaName());
            dataRow.put("proClaName",monthIncomeStatisVo.getProClaName());
            dataRow.put("month",monthIncomeStatisVo.getMonth());
            dataRow.put("averageHumen",monthIncomeStatisVo.getAverageHumen());
            dataRow.put("referenceMonth",monthIncomeStatisVo.getReferenceMonth());
            dataRow.put("averagePrice",monthIncomeStatisVo.getAveragePrice());
            dataRow.put("workloadIncome",monthIncomeStatisVo.getWorkloadIncome());
            dataRow.put("humenCostForecast",monthIncomeStatisVo.getHumenCostForecast());
            dataRow.put("grossProfit",monthIncomeStatisVo.getGrossProfit());
            dataRow.put("grossProfitMargin",monthIncomeStatisVo.getGrossProfitMargin());
            dataRow.put("type",monthIncomeStatisVo.getType());

            tableData.add(dataRow);

        }

        Map<String, Map<String, MonthIncomeStatisVo>> monthIncomeStatisVoMap = new LinkedHashMap<>();

        for(MonthIncomeStatisVo monthIncomeStatisVo : monthIncomeStatisVos){

            String areaName = monthIncomeStatisVo.getAreaName();
            String proName = monthIncomeStatisVo.getProClaName();
            Map<String, MonthIncomeStatisVo> proClaMap = monthIncomeStatisVoMap.get(areaName);

            if(CollectionUtils.isEmpty(proClaMap)){
                proClaMap = new LinkedHashMap<>();
                proClaMap.put(proName, monthIncomeStatisVo);
            }else{
                proClaMap.put(proName, monthIncomeStatisVo);
            }

            monthIncomeStatisVoMap.put(monthIncomeStatisVo.getAreaName(), proClaMap);

        }

        Map<String, Integer> mergeStartMap = new LinkedHashMap<>();
        Map<String, Integer> mergerEndMap = new LinkedHashMap<>();
        int countStart = 0;
        for(String areaName : monthIncomeStatisVoMap.keySet()){
            Map<String, MonthIncomeStatisVo> proClaMap = monthIncomeStatisVoMap.get(areaName);
            Integer mergeStart = mergeStartMap.get(areaName);
            if(mergeStart == null){
                mergeStartMap.put(areaName, countStart);
            }
            for(String proName : proClaMap.keySet()){
                countStart++;
            }
            Integer mergerEnd = mergerEndMap.get(areaName);
            if(mergerEnd == null){
                mergerEndMap.put(areaName, countStart-1);
            }
            if(areaName.equals("总计")){
                break;
            }
            JSONObject dataRowMerge= new JSONObject(true);
            dataRowMerge.put("areaName",areaName);
            dataRowMerge.put("start",mergeStartMap.get(areaName));
            dataRowMerge.put("end",mergerEndMap.get(areaName));
            tableMerge.add(dataRowMerge);
        }

        return monthIncomeStatisJsonVo;
    }

    @Override
    public void exportExcel(HttpServletResponse response, MonthIncomeStatisJsonVo monthIncomeStatisJsonVo) throws IOException {
        JSONArray tableData= monthIncomeStatisJsonVo.getTableData();
        JSONArray tableMerge = monthIncomeStatisJsonVo.getTableMerge();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"export.xlsx\"");
        // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
        XSSFWorkbook workbook = new XSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个sheet
        XSSFSheet sheet = workbook.createSheet();

        XSSFRow head = sheet.createRow(0);
        XSSFCell xssfCell0 = head.createCell(0);
        xssfCell0.setCellStyle(styles.get("header"));
        xssfCell0.setCellValue("交付区域");

        XSSFCell xssfCell1 = head.createCell(1);
        xssfCell1.setCellStyle(styles.get("header"));
        xssfCell1.setCellValue("项目分类");

        XSSFCell xssfCell2 = head.createCell(2);
        xssfCell2.setCellStyle(styles.get("header"));
        xssfCell2.setCellValue("月份");

        XSSFCell xssfCell3 = head.createCell(3);
        xssfCell3.setCellStyle(styles.get("header"));
        xssfCell3.setCellValue("平均人数");

        XSSFCell xssfCell4 = head.createCell(4);
        xssfCell4.setCellStyle(styles.get("header"));
        xssfCell4.setCellValue("参考人月");

        XSSFCell xssfCell5 = head.createCell(5);
        xssfCell5.setCellStyle(styles.get("header"));
        xssfCell5.setCellValue("平均单价");

        XSSFCell xssfCell6 = head.createCell(6);
        xssfCell6.setCellStyle(styles.get("header"));
        xssfCell6.setCellValue("工作量收入");

        XSSFCell xssfCell7 = head.createCell(7);
        xssfCell7.setCellStyle(styles.get("header"));
        xssfCell7.setCellValue("人力成本预估");

        XSSFCell xssfCell8 = head.createCell(8);
        xssfCell8.setCellStyle(styles.get("header"));
        xssfCell8.setCellValue("毛利");

        XSSFCell xssfCell9 = head.createCell(9);
        xssfCell9.setCellStyle(styles.get("header"));
        xssfCell9.setCellValue("毛利率");

        XSSFCell xssfCell10 = head.createCell(10);
        xssfCell10.setCellStyle(styles.get("header"));
        xssfCell10.setCellValue("分类");

        for (int i = 0; i < tableData.size(); i++) {
            XSSFRow row = sheet.createRow(i+1);
            JSONObject obj = (JSONObject)tableData.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                Object value = entry.getValue();
                XSSFCell xssfCell = row.createCell(j);
                xssfCell.setCellStyle(styles.get("data"));
                if(value instanceof String){
                    xssfCell.setCellValue((String)entry.getValue());
                }else if(value instanceof Integer){
                    xssfCell.setCellValue((Integer)entry.getValue());
                }else{
                    xssfCell.setCellValue(entry.getValue().toString());
                }
                j++;
            }
        }

        for (int i = 0; i < tableMerge.size(); i++) {
            JSONObject obj = (JSONObject)tableMerge.get(i);
            int startRow = obj.getInteger("start");
            int lastRow = obj.getInteger("end");
            /*
             * 设定合并单元格区域范围
             * 	firstRow  0-based
             * 	lastRow   0-based
             * 	firstCol  0-based
             * 	lastCol   0-based
             */
            CellRangeAddress cra=new CellRangeAddress(startRow+1, lastRow+1, 0, 0);

            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);

        }

        try
        {
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            logger.error("导出Excel异常{}", e);
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(response.getOutputStream());
        }

    }

    private Map<String, CellStyle> createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.LEFT);
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.RIGHT);
        styles.put("data3", style);

        return styles;
    }

    //每月统计（工作量收入）
    public MonthWorkIncomeJsonVo queryWorkloadIncome(MonthWorkIncomeQuery monthWorkIncomeQuery) {
        List<MonthWorkIncomeResultVo> workloadIncomeVos = monthIncomeStatisMapper.queryWorkloadIncome(monthWorkIncomeQuery);

        MonthWorkIncomeJsonVo monthWorkIncomeJsonVo = new MonthWorkIncomeJsonVo();

        JSONArray tableHead = new JSONArray();
        JSONArray tableData = new JSONArray();
        JSONArray tableMerge = new JSONArray();

        monthWorkIncomeJsonVo.setTableHead(tableHead);
        monthWorkIncomeJsonVo.setTableData(tableData);
        monthWorkIncomeJsonVo.setTableMerge(tableMerge);

        JSONObject headRow1 = new JSONObject(true);
        headRow1.put("columnName", "areaName");
        headRow1.put("columnComment", "交付区域");

        JSONObject headRow2 = new JSONObject(true);
        headRow2.put("columnName", "proClaName");
        headRow2.put("columnComment", "项目分类");

        tableHead.add(headRow1);
        tableHead.add(headRow2);

        Set<String> monthSet = new LinkedHashSet<>();
        for (MonthWorkIncomeResultVo monthWorkIncomeResultVo : workloadIncomeVos) {
            String month = monthWorkIncomeResultVo.getMonth();
            if (!monthSet.contains(month)) {
                monthSet.add(month);
            }
        }

        for (String str : monthSet) {
            JSONObject jsonObject = new JSONObject(true);
            jsonObject.put("columnName", str);
            jsonObject.put("columnComment", str);
            tableHead.add(jsonObject);
        }

        Map<String, Map<String, Map<String, BigDecimal>>> areaMap = new LinkedHashMap<>();

        for (MonthWorkIncomeResultVo monthWorkIncomeResultVo : workloadIncomeVos) {
            String areaName = monthWorkIncomeResultVo.getAreaName();
            String proClaName = monthWorkIncomeResultVo.getProClaName();
            String month = monthWorkIncomeResultVo.getMonth();
            BigDecimal workloadIncome = monthWorkIncomeResultVo.getWorkloadIncome();
            String key = areaName + proClaName + month;
            Map<String, Map<String, BigDecimal>> proClaMap = areaMap.get(areaName);
            if (CollectionUtils.isEmpty(proClaMap)) {
                proClaMap = new LinkedHashMap<>();
                Map<String, BigDecimal> monthMap = new LinkedHashMap<>();
                monthMap.put(month, workloadIncome);
                proClaMap.put(proClaName, monthMap);
                areaMap.put(areaName, proClaMap);
            } else {
                Map<String, BigDecimal> monthMap = proClaMap.get(proClaName);
                if (CollectionUtils.isEmpty(monthMap)) {
                    monthMap = new LinkedHashMap<>();
                    monthMap.put(month, workloadIncome);
                    proClaMap.put(proClaName, monthMap);
                } else {
                    monthMap.put(month, workloadIncome);
                }
            }
        }

        Map<String,Integer> mergeStartMap = new LinkedHashMap<>();
        Map<String,Integer> mergeEndMap = new LinkedHashMap<>();
        int countStart = 0;

        for (String areaName : areaMap.keySet()) {
            Integer mergeStart = mergeStartMap.get(areaName);
            if(null == mergeStart && !areaName.equals("总计")){
                mergeStartMap.put(areaName, countStart);
            }
            Map<String, Map<String, BigDecimal>> proClaMap = areaMap.get(areaName);
            for (String proClaName : proClaMap.keySet()) {
                Map<String, BigDecimal> monthMap = proClaMap.get(proClaName);
                JSONObject dataRow = new JSONObject(true);
                dataRow.put("areaName", areaName);
                dataRow.put("proClaName", proClaName);
                countStart++;
                if (monthMap != null) {
                    for (String month : monthSet) {
                        BigDecimal value = monthMap.get(month);
                        if (null == value) {
                            dataRow.put(month, 0);
                        } else {
                            dataRow.put(month, value);
                        }
                    }
                }
                tableData.add(dataRow);
            }
            Integer mergeEnd = mergeEndMap.get(areaName);
            if(null == mergeEnd && !areaName.equals("总计")){
                mergeEndMap.put(areaName, countStart-1);
            }
        }

        for(Map.Entry<String,Integer> mergeStartEntry: mergeStartMap.entrySet()){
            JSONObject dataRowMerge= new JSONObject(true);
            dataRowMerge.put("areaName", mergeStartEntry.getKey());
            dataRowMerge.put("start", mergeStartEntry.getValue());
            dataRowMerge.put("end", mergeEndMap.get(mergeStartEntry.getKey()));
            tableMerge.add(dataRowMerge);
        }

        return monthWorkIncomeJsonVo;
    }

    @Override
    public void exportWorkloadIncomeExcel(HttpServletResponse response, MonthWorkIncomeJsonVo monthWorkIncomeJsonVo) throws IOException {
        JSONArray tableHead = monthWorkIncomeJsonVo.getTableHead();
        JSONArray tableData= monthWorkIncomeJsonVo.getTableData();
        JSONArray tableMerge = monthWorkIncomeJsonVo.getTableMerge();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"export.xlsx\"");
        // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
        XSSFWorkbook workbook = new XSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个sheet
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow head = sheet.createRow(0);
        for (int i = 0; i < tableHead.size(); i++) {
            JSONObject obj = (JSONObject)tableHead.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                if(1==j){
                    XSSFCell xssfCell = head.createCell(i);
                    xssfCell.setCellStyle(styles.get("header"));
                    xssfCell.setCellValue(entry.getValue().toString());
                    break;
                }
                j++;
            }
        }

        for (int i = 0; i < tableData.size(); i++) {
            XSSFRow row = sheet.createRow(i+1);
            JSONObject obj = (JSONObject)tableData.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                Object value = entry.getValue();
                XSSFCell xssfCell = row.createCell(j);
                xssfCell.setCellStyle(styles.get("data"));
                if(value instanceof String){
                    xssfCell.setCellValue((String)entry.getValue());
                }else if(value instanceof Integer){
                    xssfCell.setCellValue((Integer)entry.getValue());
                }else{
                    xssfCell.setCellValue(entry.getValue().toString());
                }
                j++;
            }
        }

        for (int i = 0; i < tableMerge.size(); i++) {
            JSONObject obj = (JSONObject)tableMerge.get(i);
            int startRow = obj.getInteger("start");
            int lastRow = obj.getInteger("end");
            /*
             * 设定合并单元格区域范围
             * 	firstRow  0-based
             * 	lastRow   0-based
             * 	firstCol  0-based
             * 	lastCol   0-based
             */
            CellRangeAddress cra=new CellRangeAddress(startRow+1, lastRow+1, 0, 0);

            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);

        }

        try
        {
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            logger.error("导出Excel异常{}", e);
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(response.getOutputStream());
        }

    }

    //查询每月统计（人力成本预估）
    public MonthHumenCostJsonVo queryHumenCostForecast(MonthHumenCostQuery monthHumenCostQuery){
        List<MonthHumenCostResultVo> humenCostResultVos = monthIncomeStatisMapper.queryHumenCostForecast(monthHumenCostQuery);

        MonthHumenCostJsonVo monthHumenCostJsonVo = new MonthHumenCostJsonVo();

        JSONArray tableHead = new JSONArray();
        JSONArray tableData = new JSONArray();
        JSONArray tableMerge = new JSONArray();

        monthHumenCostJsonVo.setTableHead(tableHead);
        monthHumenCostJsonVo.setTableData(tableData);
        monthHumenCostJsonVo.setTableMerge(tableMerge);

        JSONObject headRow1 = new JSONObject(true);
        headRow1.put("columnName", "areaName");
        headRow1.put("columnComment", "交付区域");

        JSONObject headRow2 = new JSONObject(true);
        headRow2.put("columnName", "proClaName");
        headRow2.put("columnComment", "项目分类");

        tableHead.add(headRow1);
        tableHead.add(headRow2);

        Set<String> monthSet = new LinkedHashSet<>();
        for (MonthHumenCostResultVo monthHumenCostResultVo : humenCostResultVos) {
            String month = monthHumenCostResultVo.getMonth();
            if (!monthSet.contains(month)) {
                monthSet.add(month);
            }
        }

        for (String str : monthSet) {
            JSONObject jsonObject = new JSONObject(true);
            jsonObject.put("columnName", str);
            jsonObject.put("columnComment", str);
            tableHead.add(jsonObject);
        }

        Map<String, Map<String, Map<String, BigDecimal>>> areaMap = new LinkedHashMap<>();

        for (MonthHumenCostResultVo monthHumenCostResultVo : humenCostResultVos) {
            String areaName = monthHumenCostResultVo.getAreaName();
            String proClaName = monthHumenCostResultVo.getProClaName();
            String month = monthHumenCostResultVo.getMonth();
            BigDecimal humenCostForecast = monthHumenCostResultVo.getHumenCostForecast();
            String key = areaName + proClaName + month;
            Map<String, Map<String, BigDecimal>> proClaMap = areaMap.get(areaName);
            if (CollectionUtils.isEmpty(proClaMap)) {
                proClaMap = new LinkedHashMap<>();
                Map<String, BigDecimal> monthMap = new LinkedHashMap<>();
                monthMap.put(month, humenCostForecast);
                proClaMap.put(proClaName, monthMap);
                areaMap.put(areaName, proClaMap);
            } else {
                Map<String, BigDecimal> monthMap = proClaMap.get(proClaName);
                if (CollectionUtils.isEmpty(monthMap)) {
                    monthMap = new LinkedHashMap<>();
                    monthMap.put(month, humenCostForecast);
                    proClaMap.put(proClaName, monthMap);
                } else {
                    monthMap.put(month, humenCostForecast);
                }
            }
        }

        Map<String,Integer> mergeStartMap = new LinkedHashMap<>();
        Map<String,Integer> mergeEndMap = new LinkedHashMap<>();
        int countStart = 0;

        for (String areaName : areaMap.keySet()) {
            Integer mergeStart = mergeStartMap.get(areaName);
            if(null == mergeStart && !areaName.equals("总计")){
                mergeStartMap.put(areaName, countStart);
            }
            Map<String, Map<String, BigDecimal>> proClaMap = areaMap.get(areaName);
            for (String proClaName : proClaMap.keySet()) {
                Map<String, BigDecimal> monthMap = proClaMap.get(proClaName);
                JSONObject dataRow = new JSONObject(true);
                dataRow.put("areaName", areaName);
                dataRow.put("proClaName", proClaName);
                countStart++;
                if (monthMap != null) {
                    for (String month : monthSet) {
                        BigDecimal value = monthMap.get(month);
                        if (null == value) {
                            dataRow.put(month, 0);
                        } else {
                            dataRow.put(month, value);
                        }
                    }
                }
                tableData.add(dataRow);
            }
            Integer mergeEnd = mergeEndMap.get(areaName);
            if(null == mergeEnd && !areaName.equals("总计")){
                mergeEndMap.put(areaName, countStart-1);
            }
        }

        for(Map.Entry<String,Integer> mergeStartEntry: mergeStartMap.entrySet()){
            JSONObject dataRowMerge= new JSONObject(true);
            dataRowMerge.put("areaName", mergeStartEntry.getKey());
            dataRowMerge.put("start", mergeStartEntry.getValue());
            dataRowMerge.put("end", mergeEndMap.get(mergeStartEntry.getKey()));
            tableMerge.add(dataRowMerge);
        }

        return monthHumenCostJsonVo;
    }

    @Override
    public void exportHumenCostForecastExcel(HttpServletResponse response, MonthHumenCostJsonVo monthHumenCostJsonVo) throws IOException {
        JSONArray tableHead = monthHumenCostJsonVo.getTableHead();
        JSONArray tableData= monthHumenCostJsonVo.getTableData();
        JSONArray tableMerge = monthHumenCostJsonVo.getTableMerge();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"export.xlsx\"");
        // 定义一个工作薄（所有要写入excel的数据，都将保存在workbook中）
        XSSFWorkbook workbook = new XSSFWorkbook();

        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个sheet
        XSSFSheet sheet = workbook.createSheet();
        XSSFRow head = sheet.createRow(0);
        for (int i = 0; i < tableHead.size(); i++) {
            JSONObject obj = (JSONObject)tableHead.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                if(1==j){
                    XSSFCell xssfCell = head.createCell(i);
                    xssfCell.setCellStyle(styles.get("header"));
                    xssfCell.setCellValue(entry.getValue().toString());
                    break;
                }
                j++;
            }
        }

        for (int i = 0; i < tableData.size(); i++) {
            XSSFRow row = sheet.createRow(i+1);
            JSONObject obj = (JSONObject)tableData.get(i);
            Set<Map.Entry<String, Object>> entrySet = obj.entrySet();
            int j =0;
            for (Map.Entry<String, Object> entry : entrySet) {
                Object value = entry.getValue();
                XSSFCell xssfCell = row.createCell(j);
                xssfCell.setCellStyle(styles.get("data"));
                if(value instanceof String){
                    xssfCell.setCellValue((String)entry.getValue());
                }else if(value instanceof Integer){
                    xssfCell.setCellValue((Integer)entry.getValue());
                }else{
                    xssfCell.setCellValue(entry.getValue().toString());
                }
                j++;
            }
        }

        for (int i = 0; i < tableMerge.size(); i++) {
            JSONObject obj = (JSONObject)tableMerge.get(i);
            int startRow = obj.getInteger("start");
            int lastRow = obj.getInteger("end");
            /*
             * 设定合并单元格区域范围
             * 	firstRow  0-based
             * 	lastRow   0-based
             * 	firstCol  0-based
             * 	lastCol   0-based
             */
            CellRangeAddress cra=new CellRangeAddress(startRow+1, lastRow+1, 0, 0);

            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);

        }

        try
        {
            workbook.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            logger.error("导出Excel异常{}", e);
        }
        finally
        {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(response.getOutputStream());
        }

    }
}